#coding=utf-8
import sqlite3
from decimal import Decimal
from stutils import csv2data, get_dbconnector,todec
from iutils import *

tb_index = '''
create table if not exists stock_index
(id integer primary key,
 code text not null,
 name text not null,
 
 date text not null,
 
 start real not null,
 end real not null,
 high real not null,
 low real not null,
 
 volume integer not null,
 amount integer not null,
 change real,
 
 unique (code, date)
);'''

def get_dbconnector(db=None):
    if not db:db = '%s/index.db'%indexdatadir
    return sqlite3.connect(db)
    
def price_list2db(code, name, pl, con):
    cursor = con.cursor()
    pre = None; change = None
    
    for d in pl:
        
        date = d[0]
        
        start = float(d[1])
        high =  float(d[2])
        low = float(d[3])
        end = float(d[4])
        
        volume = int(d[5])*100
        amount = int( Decimal(d[6]) * 10000 )
        
        if pre:
            change = float( todec( (end - pre) * 100/ pre, 2))
            
        cursor.execute('insert into stock_index\
                        (code, name, date, start,end, high, low, volume,amount,change)\
                        values(?, ?, ?, ?, ?, ?,?, ?, ?,?)', 
                       (code, name, date, start, end, high, low, volume,amount,change))
        pre = end
        
    con.commit()
            
def initdb(con):
    cursor = con.cursor()
    cursor.execute(tb_index)
    for i in index_infos:
        name = i[0];code = i[2]
        fn = '%s/data/%s.csv'%(indexdatadir, name)
        data = csv2data(fn)
        price_list2db(code, name, data, con)
        
def update(con):
    pass

class Price(object):

    def __init__(self, queryset):
        
        self.date = queryset[3]
        
        self.start = queryset[4]
        self.end = queryset[5]
        self.high = queryset[6]
        self.high = queryset[7]
        
        self.volume = queryset[8]
        self.amount = queryset[9]
        self.change = queryset[10]
                
class Index(object):
                
    def __init__(self, con, name='szzs'):
        self.con = con
        self.name = name
        
    def price_on(self, exdate):
        c = self.con.cursor()
        c.execute('select * from stock_index where name=? and date=?', 
                 (self.name, exdate))
        r = c.fetchone()
        if not r:return None
        return Price(r)
        
    def price_after(self, exdate, n=1):
        c = self.con.cursor()
        c.execute('select * from stock_index where name=? and date>?  order by date limit ? ',
                 (self.name, exdate, n))
        r = c.fetchall()
        if not r:return None
        if n == 1:
            return Price(r[0])
        else:
            ps = []
            for d in r:
                ps.append(Price(d))
            return ps
                
    def price_before(self, exdate, n=1):
        c = self.con.cursor()
        c.execute('select * from stock_index where name=? and date<? order by date desc limit ?',
                 (self.name, exdate, n))
                 
        r = c.fetchall()
        if not r:return None
        if n == 1:
            return Price(r[0])
        else:
            ps = []
            for d in r:
                ps.append(Price(d))
            return ps
            
    def ap_on(self, exdate, n=5):
        ps = self.price_before(exdate, n)
        if not ps or len(ps) != n:
            return None
        es = [p.end for p in ps]
        return sum(es)/n
        
    def av_on(self, exdate, n=5):
        ps = self.price_before(exdate, n)
        if not ps or len(ps) != n:
            return None
        vs = [p.volume for p in ps]
        return sum(vs)/float(n)
    
